package org.budo.graph.service.util;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import java.util.Map;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;

import javax.sql.DataSource;

import org.budo.graph.view.util.DruidDataSourceUtil;
import org.budo.support.google.common.cache.GoogleCacheUtil;
import org.budo.support.javax.sql.util.JdbcUtil;
import org.budo.support.lang.util.ReflectUtil;
import org.budo.support.lang.util.StringUtil;
import org.budo.support.servlet.util.QueryStringUtil;
import org.budo.support.slf4j.Slf4j;
import org.slf4j.Logger;
import org.springframework.context.ApplicationContext;

import com.alibaba.druid.pool.DruidDataSource;
import com.google.common.cache.Cache;

import io.shardingsphere.core.jdbc.core.datasource.MasterSlaveDataSource;

/**
 * @author lmw
 */
public class DatabaseMetadataUtil {
    private static final Logger log = Slf4j.getLogger();

    private static final Cache _TABLE_META_DATA_CACHE = GoogleCacheUtil.newBuilder("_TABLE_META_DATA_CACHE") //
            .expireAfterWrite(5 * 60, TimeUnit.SECONDS) // 120 秒一定会过期
            .expireAfterAccess(2 * 60, TimeUnit.SECONDS) // 60 秒没用就过期
            .recordStats() // 统计
            .build();

    private static Cache _DB_TABLE_CACHE = GoogleCacheUtil.newBuilder("_DB_TABLE_CACHE") //
            .expireAfterWrite(5 * 60, TimeUnit.SECONDS) // 120 秒一定会过期
            .expireAfterAccess(2 * 60, TimeUnit.SECONDS) // 60 秒没用就过期
            .recordStats() // 统计
            .build();

    private static final Boolean HAS_SHARDING = ReflectUtil.hasClass("io.shardingsphere.core.jdbc.core.datasource.MasterSlaveDataSource");

    public static String writeDatabaseMetadataResponse(ApplicationContext applicationContext, String url) {
        Map<String, String> parameters = QueryStringUtil.queryStringToStringMap(url);
        String count = parameters.get("count");

        String[] beanNames = applicationContext.getBeanNamesForType(DataSource.class);
        if (null == beanNames || beanNames.length < 1) {
            log.error("#30 beanNames for DataSource.class is null or empty, beanNames=" + StringUtil.join(beanNames) + ", applicationContext=" + applicationContext);
            return "NoDataSource";
        }

        String result = "<pre>";
        for (String beanName : beanNames) {
            DataSource dataSource = (DataSource) applicationContext.getBean(beanName);
            result += "\n" + dataSourceToString(dataSource) + "\n";
            result += getDatabaseMetaData(dataSource, count);
            result += "\n";
        }

        result += "</pre>";
        return result;

    }

    private static String dataSourceToString(DataSource dataSource) {
        if (null == dataSource) {
            return null;
        }

        if (dataSource instanceof DruidDataSource) {
            return "Druid@" + Integer.toHexString(dataSource.hashCode()) + " " + ((DruidDataSource) dataSource).getUrl();
        }

        return dataSource.getClass().getName() + "@" + Integer.toHexString(dataSource.hashCode());
    }

    public static String getDatabaseMetaData(DataSource dataSource, String count) {
        if (null == dataSource) {
            log.error("getDatabaseMetaData, dataSource is null, dataSource=" + dataSource);
            return "dataSource is null";
        }

        if (HAS_SHARDING && dataSource instanceof MasterSlaveDataSource) {
            return "MasterSlaveDataSource";
        }

        return doGetDatabaseMetaData(dataSource, count);
    }

    private static String doGetDatabaseMetaData(DataSource dataSource, String count) {
        List<Map<String, Object>> tableMaps = getTablesCached(dataSource); // oracle 这个也耗时

        // oracle 的话 这里包含所有表
        log.info("#102 dataSource=" + dataSource + ", tableMapList.size=" + tableMaps.size());

        List<TableMetaData> tableMetaDatas = new ArrayList<TableMetaData>();
        for (Map<String, Object> tableMap : tableMaps) {
            TableMetaData tableMetaData = tableMetaDataCached(dataSource, tableMap, count);
            if (null == tableMetaData) {
                continue;
            }

            tableMetaDatas.add(tableMetaData);
        }

        // 对所有表排序 根据记录数
        Collections.sort(tableMetaDatas, new Comparator<TableMetaData>() {
            public int compare(TableMetaData o1, TableMetaData o2) {
                Integer recordCount1 = o1.getRecordCount();
                Integer recordCount2 = o2.getRecordCount();

                if (null == recordCount1 || null == recordCount2) {
                    return o2.getMetaSql().compareTo(o1.getMetaSql());
                }

                return recordCount2.compareTo(recordCount1);
            }
        });

        return StringUtil.join(tableMetaDatas.stream().map(e -> {
            return "/*" + e.getRecordCount() + "*/ " + e.getMetaSql();
        }).collect(Collectors.toList()), "\n");
    }

    private static List<Map<String, Object>> getTablesCached(DataSource dataSource) {
        String key = dataSource + "-" + DruidDataSourceUtil.getUrl(dataSource);

        return (List<Map<String, Object>>) GoogleCacheUtil.get(_DB_TABLE_CACHE, key, () -> {
            return JdbcUtil.getTables(dataSource);
        });
    }

    private static TableMetaData tableMetaDataCached(DataSource dataSource, Map<String, Object> tableMap, String count) {
        String owner = (String) tableMap.get("TABLE_SCHEM"); // TABLE_SCHEM owner
        String tableName = (String) tableMap.get("TABLE_NAME");

        String key = owner + "-" + tableName + "-" + count;

        return (TableMetaData) GoogleCacheUtil.get(_TABLE_META_DATA_CACHE, key, () -> {
            return tableMetaData_0(dataSource, tableMap, count);
        });
    }

    private static TableMetaData tableMetaData_0(DataSource dataSource, Map<String, Object> tableMap, String count) {
        log.info("#106 tableMap=" + tableMap);

        String tableName = (String) tableMap.get("TABLE_NAME");

        String sql = "no MySQL or Oracle";
        if (DruidDataSourceUtil.isMySQL(dataSource)) {
            sql = "DESC `" + tableName + "`";
        } else if (DruidDataSourceUtil.isOracle(dataSource)) {
            String username = DruidDataSourceUtil.getUsername(dataSource);

            String owner = (String) tableMap.get("TABLE_SCHEM"); // TABLE_SCHEM owner
            if (!StringUtil.equalsIgnoreCase(username, owner)) {
                return null; // 不是我的表 跳过
            }

            sql = "SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = UPPER('" + tableName + "') AND OWNER = UPPER('" + username + "')"; // Oracle 表结构
        } else {
            log.error("#111 connection is not MySQL or Oracle, dataSource=" + dataSource + ", tableName=" + tableName);
            return null;
        }

        List<Map<String, Object>> columnMaps = JdbcUtil.executeQuery(dataSource, sql);

        List<String> tablePrimaryKeys = new ArrayList<String>();

        List<String> columnMetaDatas = new ArrayList<String>();
        for (Map<String, Object> columnMap : columnMaps) {
            String columnMetaData = "";//

            Object columnName = columnMap.get("Field");
            if (null == columnName) {
                columnName = columnMap.get("COLUMN_NAME");
            }
            columnMetaData += "`" + columnName + "`";//

            Object _columnType = columnMap.get("Type");
            if (null == _columnType) {
                _columnType = columnMap.get("DATA_TYPE");
            }

            String columnType = sortEnumValues((String) _columnType);
            columnMetaData += " " + columnType; //

            Object isNullable = columnMap.get("Null");
            if (null == columnName) {
                columnName = columnMap.get("NULLABLE");
            }
            if (StringUtil.equalsIgnoreCase("NO", isNullable + "")) {
                columnMetaData += " NOT NULL";
            }

            Object isAutoincrement = columnMap.get("IS_AUTOINCREMENT");
            if (StringUtil.equalsIgnoreCase("YES", isAutoincrement + "")) {
                columnMetaData += " AUTO_INCREMENT";
            }

            Object extra = columnMap.get("Extra");
            if (null != extra && null != extra && !extra.toString().trim().isEmpty()) {
                columnMetaData += " " + extra;
            }

            Object columnDefault = columnMap.get("Default");
            if (null != columnDefault) {
                if ("bit(1)".equalsIgnoreCase(columnType) // 特定情况的SQL默认值不加引号
                        || "CURRENT_TIMESTAMP".equals(columnDefault)) { // 默认值不加引号
                    columnMetaData += " DEFAULT " + columnDefault;
                } else { // 加引号
                    columnMetaData += " DEFAULT '" + columnDefault + "'";
                }
            }

            if ("PRI".equals(columnMap.get("Key"))) {
                tablePrimaryKeys.add((String) columnName);
            }

            columnMetaDatas.add(columnMetaData);
        }

        // 对所有列排序
        Collections.sort(columnMetaDatas);

        // 数据库表 数据行数
        Integer recordCount = null;
        if (null == count || "null".equalsIgnoreCase(count.trim()) || !"false".equalsIgnoreCase(count.trim())) {
            String countSql = "SELECT COUNT(*) FROM " + tableName;
            recordCount = JdbcUtil.executeFind(dataSource, countSql, Integer.class);
        }

        String tableMetaSql = "TABLE `" + tableName + "` ( \n\t";//
        tableMetaSql += StringUtil.join(columnMetaDatas, ", \n\t");

        // 支持复合主键 PRIMARY KEY (`project_id`,`engineer_id`),
        if (!tablePrimaryKeys.isEmpty()) {
            tableMetaSql += ",\n\tPRIMARY KEY (" + StringUtil.join(tablePrimaryKeys, ",", "`", "`") + ")";
        }

        tableMetaSql += "\n); ";

        return new TableMetaData(tableMetaSql, recordCount);
    }

    /**
     * 枚举类型排序
     */
    private static String sortEnumValues(String typeString) {
        if (!StringUtil.startsWith(typeString, "enum('")) {
            return typeString;
        } else {
            String valueString = typeString.replace("enum(", "").replace(")", "");
            String[] values = valueString.split(",");

            Arrays.sort(values);

            return "enum(" + StringUtil.join(values, ",\n\t\t") + ")";
        }
    }
}